package com.zt.rongda.bus.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.zt.rongda.bus.inter.BusHistoryMapper;
import com.zt.rongda.bus.inter.BusMapper;
import com.zt.rongda.bus.model.Bus;
import com.zt.rongda.bus.model.BusHistory;
import com.zt.rongda.constant.BusConstant;
import com.zt.rongda.util.JSONUtil;
import com.zt.servlet.ReqBean;
import com.zt.system.model.BusinessException;
import com.zt.system.model.SqlBuilder;
import com.zt.system.service.BaseService;
import com.zt.util.PropertiesUtil;
import com.zt.util.StringUtil;

@Service
public class BusService extends BaseService {

    @Autowired
    private BusMapper busMapper;

    @Autowired
    private BusHistoryMapper busHistoryMapper;

    public static String DRIVER_NAME = PropertiesUtil.getProperty("sqlServer.driverName");

    public static String DB_URL = PropertiesUtil.getProperty("sqlServer.dbURL");

    public static String USERNAME = PropertiesUtil.getProperty("sqlServer.userName");

    public static String PASSWORD = PropertiesUtil.getProperty("sqlServer.password");

    /**
     * 查询
     * 
     * @param reqBean
     * @return
     */
    public JSONObject getTable(ReqBean reqBean) {
        SqlBuilder sqlBuilder = new SqlBuilder("");
        sqlBuilder.appendSql("select * from MEDIA_BUS a where 1=1 ");
        sqlBuilder.addWhereLike2("a.VEHICLE_NO", reqBean.getInfo("vehicleNo"));
        sqlBuilder.addWhereLike2("a.VEHICLE_NO_ABBR", reqBean.getInfo("vehicleNoAbbr"));
        sqlBuilder.addWhereLike2("a.PLATE_NO", reqBean.getInfo("plateNo"));
        sqlBuilder.addWhere("a.LINE_NO", reqBean.getInfo("lineNo"));
        sqlBuilder.addWhere("a.COMPANY_NAME", reqBean.getInfo("companyName"));
        return queryTable(sqlBuilder, reqBean.getPageSize(), reqBean.getPageIndex());
    }

    /**
     * 通过id查询
     * 
     * @param busId
     * @return
     */
    public JSONObject getById(String busId) {
        Bus b = busMapper.selectByPrimaryKey(busId); 
        return b == null ? null : JSONObject.fromObject(b,JSONUtil.getJsonConfig());
    }

    /**
     * 新增
     * 
     * @param bus
     */
    public void add(Bus bus) {
        bus.setBusId(intIncrementId("MEDIA_BUS", "BUS_ID"));
        bus.setMakeTime(new Date());
        bus.setModifyTime(bus.getMakeTime());
        busMapper.insert(bus);
    }

    /**
     * 修改
     * 
     * @param bus
     */
    public void update(Bus bus) {
        String busId = bus.getBusId();
        if (StringUtil.isEmpty(busId)) {
            throw new BusinessException("获取主键失败");
        }

        bus.setModifyTime(new Date());
        busMapper.updateByPrimaryKeySelective(bus);
    }

    /**
     * 删除
     * 
     * @param busId
     */
    public void delete(String busId) {
        if (StringUtil.isEmpty(busId)) {
            throw new BusinessException("获取主键失败");
        }
        busMapper.deleteByPrimaryKey(busId);
    }

    // 新增车辆异动信息
    public void addBusHistory(Bus bus, BusHistory busHistory) {
        // 插入最新信息至车辆表
        String busId = bus.getBusId();
        if (StringUtil.isEmpty(busId)) {
            throw new BusinessException("获取主键失败");
        }

        bus.setModifyTime(new Date());
        busMapper.updateByPrimaryKeySelective(bus);

        // 插入数据至异动表
        busHistory.setId(intIncrementId("MEDIA_BUS_HISTORY", "ID"));
        busHistory.setMakeTime(new Date());
        busHistory.setModifyTime(new Date());
        busHistoryMapper.insert(busHistory);
    }

    public JSONObject transQuery(ReqBean reqBean) {
        String busId = reqBean.getInfo("busId");
        if (StringUtils.isBlank(busId)) {
            throw new BusinessException("缺少参数!");
        }

        Connection conn = null;
        PreparedStatement pstmt1 = null;
        ResultSet rs1 = null;

        PreparedStatement pstmt2 = null;
        ResultSet rs2 = null;

        PreparedStatement pstmt3 = null;
        ResultSet rs3 = null;

        JSONObject returnJson = new JSONObject();
        JSONArray companyInnerTransArr = new JSONArray();

        JSONArray companyTransArr = new JSONArray();

        JSONArray vehicleUsingArr = new JSONArray();

        try {
            Class.forName(DRIVER_NAME);
            conn = DriverManager.getConnection(DB_URL, USERNAME, PASSWORD);

            String sql = "SELECT a.CompanyIn companyIn,a.CompanyOut companyOut,a.LineIn lineIn,a.LineOut lineOut,"
                    + "a.VehTransfer_ID vehTransferId,a.VehTransfer_Reason reason,a.VehTransfer_Time vehTransferTime,"
                    + "v.Veh_RegCode plateNo,v.Veh_Code vehicleNo from Tbl_TE_BatchVehicleTransfer a "
                    + "LEFT JOIN Tbl_TE_BatchVehicleTransferDetail d ON d.VehTransfer_ID = a.VehTransfer_ID "
                    + "LEFT JOIN Tbl_Core_Vehicle v ON v.Veh_ID = d.Vehicle_ID " + "where  a.VehTransfer_Status = '4' "
                    + "and v.Veh_ID = '" + busId + "'";

            sql = sql + " order by a.VehTransfer_Time desc";
            pstmt1 = conn.prepareStatement(sql);
            rs1 = pstmt1.executeQuery();
            while (rs1.next()) {
                JSONObject data = new JSONObject();
                data.put("plateNo", rs1.getString("plateNo"));
                data.put("vehicleNo", rs1.getString("vehicleNo"));
                data.put("lineIn", rs1.getString("lineIn"));
                data.put("companyIn", rs1.getString("companyIn"));
                data.put("companyOut", rs1.getString("companyOut"));
                data.put("lineOut", rs1.getString("lineOut"));
                data.put("vehTransferId", rs1.getString("vehTransferId"));
                data.put("reason", rs1.getString("reason"));
                data.put("vehTransferTime", rs1.getString("vehTransferTime"));
                data.put("type", BusConstant.Bus.BUS_TRANS_TYPE_INNER);
                companyInnerTransArr.add(data);
            }
            rs1.close();
            pstmt1.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs1.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                pstmt1.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        try {
            String sql = "SELECT b.Veh_RegCode plateNo,b.Veh_Code vehicleNo,a.CompanyOut companyOut,a.VehTransfer_Reason reason,"
                    + "a.CompanyIn companyIn,a.VehTransfer_Plan_Datetime planTime,a.VehTransfer_Time vehTransferTime "
                    + "FROM Tbl_TE_VehicleTransfer AS a "
                    + "LEFT JOIN Tbl_Core_Vehicle AS b ON b.Veh_ID = a.Vehicle_Id "
                    + "where a.VehTransfer_Type = '99CBCBF8-0F68-4190-BA74-724F88EBEA18' AND a.VehTransfer_Status = '4' "
                    + "and b.Veh_ID = '" + busId + "'";

            sql = sql + " order by a.VehTransfer_Time desc";
            pstmt2 = conn.prepareStatement(sql);
            rs2 = pstmt2.executeQuery();
            while (rs2.next()) {
                JSONObject data = new JSONObject();
                data.put("plateNo", rs2.getString("plateNo"));
                data.put("vehicleNo", rs2.getString("vehicleNo"));
                data.put("companyOut", rs2.getString("companyOut"));
                data.put("companyIn", rs2.getString("companyIn"));
                data.put("reason", rs2.getString("reason"));
                data.put("vehTransferTime", rs2.getString("vehTransferTime"));
                data.put("type", BusConstant.Bus.BUS_TRANS_TYPE_COMPANY);
                companyTransArr.add(data);
            }
            rs2.close();
            pstmt2.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                rs2.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                pstmt2.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        try {

            String sql = "SELECT b.Veh_RegCode plateNo,b.Veh_Code vehicleNo,a.CompanyOut companyOut,a.VehTransfer_Reason reason,"
                    + "a.VehTransfer_Before_BusKind beforeBusKind,a.VehTransfer_After_BusKind afterBusKind,d1.DictItem_DisplayName beforeBusSort,"
                    + "d2.DictItem_DisplayName afterBusSort,a.CompanyIn companyIn,a.VehTransfer_Time vehTransferTime "
                    + "FROM Tbl_TE_VehicleTransfer AS a "
                    + "LEFT JOIN Tbl_Core_Vehicle AS b ON b.Veh_ID = a.Vehicle_Id "
                    + "LEFT JOIN Tbl_TE_DictItem d1 ON d1.DictItem_ID = a.VehTransfer_Before_VehMod_Sort "
                    + "LEFT JOIN Tbl_TE_DictItem d2 ON d2.DictItem_ID = a.VehTransfer_After_VehMod_Sort "
                    + "where a.VehTransfer_Type = '3EE90170-4143-4A25-B0F0-AC2F7693DC3D' AND a.VehTransfer_Status = '4' "
                    + "and b.Veh_ID = '" + busId + "'";

            sql = sql + " order by a.VehTransfer_Time desc";
            pstmt3 = conn.prepareStatement(sql);
            rs3 = pstmt3.executeQuery();
            while (rs3.next()) {
                JSONObject data = new JSONObject();
                data.put("plateNo", rs3.getString("plateNo"));
                data.put("vehicleNo", rs3.getString("vehicleNo"));
                data.put("beforeBusKind", rs3.getString("beforeBusKind"));
                data.put("afterBusKind", rs3.getString("afterBusKind"));
                data.put("beforeBusSort", rs3.getString("beforeBusSort"));
                data.put("afterBusSort", rs3.getString("afterBusSort"));
                data.put("companyIn", rs3.getString("companyIn"));
                data.put("companyOut", rs3.getString("companyOut"));
                data.put("reason", rs3.getString("reason"));
                data.put("vehTransferTime", rs3.getString("vehTransferTime"));
                data.put("type", BusConstant.Bus.BUS_TRANS_TYPE_USING);
                vehicleUsingArr.add(data);
            }
            rs3.close();
            pstmt3.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                rs3.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                pstmt3.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        returnJson.put("companyInnerTransList", companyInnerTransArr);
        returnJson.put("companyTransList", companyTransArr);
        returnJson.put("vehicleUsingList", vehicleUsingArr);

        return returnJson;
    }

    /**
     * 广告历史
     * 
     * @param busId
     * @return
     */
    public JSONArray adHistory(String busId) {
        if (StringUtil.isEmpty(busId)) {
            throw new BusinessException("非法请求!");
        }
        String sql = "SELECT a.BUS_ID,a.VEHICLE_NO,a.VEHICLE_NO_ABBR,a.PLATE_NO,"
                + "a.LINE_NO,a.COMPANY_NAME,o.CUSTOMER_ID,i.AD_NAME,o.FINISH_TIME FROM `MEDIA_BUS` a "
                + "LEFT JOIN AD_ORDER_ITEM i ON i.BUS_ID = a.BUS_ID "
                + "LEFT JOIN AD_ORDER o ON o.ORDER_ID = i.ORDER_ID AND o.`STATUS` = ? "
                + "where a.BUS_ID = ? ORDER BY o.FINISH_TIME desc";
        return query(sql, BusConstant.OrderStatus.FINISHED, busId);

    }
}